// 引入mysql模块
const mysql = require('mysql2')
// 引入数据库配置信息文件
const config = require('../config/config.js')

// 创建数据库连接对象
const db = mysql.createConnection({
    host: config.database.HOST,
    user: config.database.USER,
    password: config.database.PASSWORD,
});


const pool = mysql.createPool({
    host: config.database.HOST,
    user: config.database.USER,
    password: config.database.PASSWORD,
    database: config.database.DATABASE,
})

let query = (sql, values) => {
    return new Promise((resolve, reject) => {
        pool.getConnection((err, connection) => {
            if (err) {
                reject(err);
            } else {
                connection.query(sql, values, (err, rows) => {
                    // 释放连接应该放在这里，以确保无论是否出错都会释放
                    connection.release();
                    if (err) {
                        reject(err);
                    } else {
                        resolve(rows);
                    }
                });
            }
        });
    });
}


// 新建wall
exports.insterWall = (value) => {
    let _sql = 'insert into walls set type = ?,message = ?,name = ?,userId = ?,moment = ?,label = ?,color = ?, src = ?'
    return query(_sql,value)
}


// 新建反馈
exports.insterFeedback = (value) => {
    let _sql = 'insert into feedbacks set wallId = ?, userId = ?, type = ?, moment = ?'
    return query(_sql,value)
}

// 新建评论
exports.insertComment = (value) => {
    let _sql = 'insert into comments set wallId = ?, userId = ?, src = ?, moment = ?, comment = ?, name = ?'
    return query(_sql,value)
}

// 删除墙，主表对应多条子表一起删除
exports.deleteWall = (id) => {
    let _sql = `delete a,b,c from walls a left join feedbaks b on a.id = b.wallId left join comments c on a.id = c.wallId where a.id = ${ id }`
    return query(_sql)
}

// 删除反馈
exports.deleteFeedback = (id) => {
    let _sql = `delete from feedbacks where id = ${ id }`
    return query(_sql)
}

// 删除评论
exports.deleteComment = (id) => {
    let _sql = `delete from comments where id = ${ id }`
    return query(_sql)
}

// 分页查询
exports.findWallPage = (page, pagesize, type, label) => {
    let _sql;
    if(label == 0) {
        _sql = `select * from walls where type = ${type} order by moment desc limit ${(page - 1) * pagesize},${pagesize}`
    }
    else {
        _sql = `select * from walls where type = ${type} and label = '${label}' order by moment desc limit ${(page - 1) * pagesize},${pagesize}`
    }
    return query(_sql)
}

// 查询评论
exports.findCommentPage = (page, pagesize, id) => {
    let _sql = `select * from comments where wallId = ${id} order by id desc limit ${(page - 1) * pagesize},${pagesize}`
    return query(_sql)
}

// 查询反馈
exports.feedbackCount = (wid, type) => {
    let _sql = `select count(*) as count from feedbacks where wallId = ${wid} and type = ${type}`
    return query(_sql)
}

// 查询评论数
exports.commentCount = (wid) => {
    let _sql =`select count(*) as count from comments where wallId = ${wid}`
    return query(_sql)
}

// 是否点赞
exports.likeCount = (wid, uid) => {
    let _sql = `select count(*) as count from feedbacks where wallId = ${wid} and userId = ${uid} and type = 0`
    return query(_sql)
}